#include "rollcadresearchresultdialog.h"
#include "ui_rollcadresearchresultdialog.h"
#include <QSqlQueryModel>
#include "timeeditdelegate.h"
#include <QSqlRecord>
#include <QDebug>
#include <QVariant>
#include <QHash>
#include "cadreroll.h"
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QVariantList>
#include <QApplication>
#include <QAbstractItemView>
#include <QColor>
#include <QDate>

RollCadreSearchResultDialog::RollCadreSearchResultDialog(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::RollCadreSearchResultDialog)
{
    ui->setupUi(this);
}

RollCadreSearchResultDialog::~RollCadreSearchResultDialog()
{
    delete ui;
}
// 过滤条件设置
void RollCadreSearchResultDialog::setFilter()
{
    if(filterHash.isEmpty()){
        return;
    }
    QString cadrename = filterHash.value("CadreName")[0];
    QString unitname = filterHash.value("UnitName")[0];
    QString postname = filterHash.value("PostName")[0];
    QString experpost = filterHash.value("ExperPost")[0];
    QString experunit = filterHash.value("ExperUnit")[0];
    QString school = filterHash.value("School")[0];
    QString major = filterHash.value("Major")[0];
    QString workschool = filterHash.value("WorkSchool")[0];
    QString workmajor = filterHash.value("WorkMajor")[0];
    QString familyunit = filterHash.value("FamilyUnit")[0];
    QString familypro = filterHash.value("FamilyPro")[0];
    QString filerecv = filterHash.value("FileNum")[0];
    QStringList native = filterHash.value("NativePlace");
    QStringList birthplace = filterHash.value("Birthplace");
    QStringList growplace = filterHash.value("GrowPlace");
    proxyModel->setCadreName(cadrename);
    proxyModel->setUnitName(unitname);
    proxyModel->setPostName(postname);
    proxyModel->setExperPost(experpost);
    proxyModel->setExperUnit(experunit);
    proxyModel->setSchool(school);
    proxyModel->setMajor(major);
    proxyModel->setWorkSchool(workschool);
    proxyModel->setWorkMajor(workmajor);
    proxyModel->setFamilyUnit(familyunit);
    proxyModel->setFamilyPro(familypro);
    proxyModel->setFileRecv(filerecv);
    proxyModel->setNativePlace(native);
    proxyModel->setBirthplace(birthplace);
    proxyModel->setGrowPlace(growplace);



    QStringList birth = filterHash.value("Birth");
    QStringList age = filterHash.value("age");
    QStringList joinworktime = filterHash.value("JoinWorkTime");
    QStringList partytime = filterHash.value("PartyTime");
//    QStringList posttime = filterHash.value("PostTime");
//    QStringList postyear = filterHash.value("PostYear");
    QStringList ranktime = filterHash.value("RankTime");
    QStringList rankyear = filterHash.value("RankYear");
    QStringList managestatus = filterHash.value("ManageStatus");
    QStringList threeone = filterHash.value("ThreeOne");
    QStringList checkyear = filterHash.value("CheckYear");
    QStringList expernull = filterHash.value("ExperNull");
    proxyModel->setBirth(birth);
    proxyModel->setAge(age);
    proxyModel->setJoinWorkTime(joinworktime);
    proxyModel->setPartyTime(partytime);
    proxyModel->setRankTime(ranktime);
    proxyModel->setRankYear(rankyear);
//    proxyModel->setPostTime(posttime);
//    proxyModel->setPostYear(postyear);
    proxyModel->setManageStatus(managestatus);
    proxyModel->setThreeOne(threeone);
    proxyModel->setExperNull(expernull);
    proxyModel->setCheckYear(checkyear);


    QStringList sexList = filterHash.value(QString::fromUtf8("性别"));
    QStringList politicList = filterHash.value(QString::fromUtf8("政治面貌"));
    QStringList nationList = filterHash.value(QString::fromUtf8("民族"));
    QStringList rankList = filterHash.value(QString::fromUtf8("职务级别"));
    QStringList postcategoryList = filterHash.value(QString::fromUtf8("职务属性"));
    QStringList isfileList = filterHash.value(QString::fromUtf8("已录数字档案"));
//    QStringList archiveclassList = filterHash.value(QString::fromUtf8("档案类别"));
    QStringList managestatusList = filterHash.value(QString::fromUtf8("干部状态"));
    QStringList workeducationList = filterHash.value(QString::fromUtf8("在职学历"));
    QStringList workdegreeList = filterHash.value(QString::fromUtf8("在职学位"));
    QStringList educationList = filterHash.value(QString::fromUtf8("全日制学历"));
    QStringList degreeList = filterHash.value(QString::fromUtf8("全日制学位"));
    QStringList unitList = filterHash.value(QString::fromUtf8("单位名称"));
    QStringList unitgradationList = filterHash.value(QString::fromUtf8("单位层次"));
    QStringList unitnatrueList = filterHash.value(QString::fromUtf8("单位属性"));
    QStringList unitcategoryList = filterHash.value(QString::fromUtf8("单位性质"));
    QStringList unitofcategoryList = filterHash.value(QString::fromUtf8("单位类别"));
    QStringList unitlevelList = filterHash.value(QString::fromUtf8("单位级别"));
    QStringList subunitList = filterHash.value(QString::fromUtf8("单位隶属关系"));
    proxyModel->setSex(sexList);
    proxyModel->setPolitic(politicList);
    proxyModel->setNation(nationList);
    proxyModel->setRank(rankList);
    proxyModel->setRank(postcategoryList);
    proxyModel->setFile(isfileList);
    proxyModel->setManageStatusList(managestatusList);
    proxyModel->setWorkEducationList(workeducationList);
    proxyModel->setWorkDegreeList(workdegreeList);
    proxyModel->setEducationList(educationList);
    proxyModel->setDegreeList(degreeList);
    proxyModel->setUnitList(unitList);
    proxyModel->setUnitGradationList(unitgradationList);
    proxyModel->setUnitNatrueList(unitnatrueList);
    proxyModel->setUnitCategoryList(unitcategoryList);
    proxyModel->setUnitOfCategoryList(unitofcategoryList);
    proxyModel->setUnitLevelList(unitlevelList);
    proxyModel->setSubUnitList(subunitList);

    QString orrelation = filterHash.value("OrRelation")[0];
    proxyModel->setOrRelation(orrelation);

    ui->resultTableView->update();
}

void RollCadreSearchResultDialog::receiveData(QHash<QString,QStringList> hash)
{
    filterHash=hash;
}

void RollCadreSearchResultDialog::initResultTable(QString cadrename,bool samename)
{
    tableModel = new ResultTableModel(this);
    // 显示表第一列是checkbox
    QString sqlstring=" SELECT '', `CadreName`, `UsedName`, cc.`CredType`, ci.`IDNum`, "
               "`Birth`, `JoinWorkTime`, `Sex`, `Health`, nationc.`Nation`, "
               " area.`AdminArea`, `BirthPlace`, `GrowPlace`, politicc.`Politic`, "
               "`PartyTime`,`DemoParty`, `RetireChange`, `Speciality`, "
               "msc.`ManageStatus`, `IdenCate`, `ThreeOne`, `Phone`, "
               "`SubstratumWork`,rc.`RankName`, `RankTime`, "
               "`RankSalary`, uc.unitname,p1.`PostName`,p.`PostCategory`,pmin.minposttime, edu.`WorkORSchool`, "
               "edu.`Major`,edu.`School`,file.`FileNum`,exper.`PostName` as experPostName, "
               "exper.`UnitName` as experUnitName , f.`Profession` as fprofession, "
               "f.`UnitName` as funitname, ugc.`UnitGradationName`, unc.`UnitNatrueName`, "
               "ucc.`UnitCategory`, uocc.`UnitOfCategoryName`,ulc.`UnitLevel`,suc.`AffiliationName`, "
               "ec.`Education`,dc.`Degree`,ch.mincheckyear, ch.maxcheckyear ,ch.countcheckyear "
               "FROM ZZB.CadreInfo ci "
               "left join ZZB.AdminAreaCode area on area.`AdminAreaID`=ci.`NativePlace` "
               "left join ZZB.CredTypeCode cc on ci.`CredTypeID`= cc.`CredTypeID` "
               "left join ZZB.NationCode nationc on nationc.`NationID`=ci.`NationID` "
               "left join ZZB.PoliticCode politicc on politicc.`PoliticID`=ci.`PoliticID` "
               "left join ZZB.ManageStatusCode msc on msc.`ManageStatusID`=ci.`ManageStatusID` "
               "left join ZZB.unit uc on uc.`UnitID`=ci.`UnitID` "
               "left join ZZB.Post p  on p.`IDNum` = ci.`IDNum` "
               "left join (select GROUP_CONCAT(`PostName`) as postname, "
                      "`IDNum` as `pIDNum` from Post where QualifyType='0' group by `IDNum`) p1 "
                      "on p1.pIDNum=p.`IDNum` "
               "left join (select min(`PostTime`) as minposttime,`IDNum` "
                      "from Post group by `IDNum`) pmin on pmin.`IDNum`=p.`IDNum` "
               "left join ZZB.RankCode rc on rc.`RankID`=ci.`RankID` "
               "left join ZZB.Edu edu on edu.`IDNum` = ci.`IDNum` "
               "left join ZZB.Experience exper on exper.`IDNum`= ci.`IDNum` "
               "left join ZZB.Family f on f.`IDNum` = ci.`IDNum` "
               "left join ZZB.File file on file.`IDNum` = ci.`IDNum` "
               "left join ZZB.UnitGradationCode ugc on ugc.`UnitGradationId`=uc.unitgradation "
               "left join ZZB.UnitNatrueCode unc on unc.`UnitNatrueId`=uc.unitnatrue "
               "left join ZZB.UnitCategoryCode ucc on ucc.`UnitCategoryID`=uc.unitproperty "
               "left join ZZB.UnitOfCategoryCode uocc on uocc.`UnitOfCategoryId`=uc.unitcategory "
               "left join ZZB.UnitLevelCode ulc on ulc.`UnitLevelID`=uc.unitlevel "
               "left join ZZB.AffiliationCode suc on suc.`AffiliationId`=uc.`Affiliation` "
               "left join ZZB.EducationCode ec on ec.`EducationID`=edu.`EduName` "
               "left join ZZB.DegreeCode dc on dc.`DegreeID`=edu.`AcademicDe` "
               "left join (SELECT `IDNum`,max(`CheckYear`) as maxcheckyear,min(`CheckYear`) as mincheckyear,"
                      "count(`CheckYear`) as countcheckyear "
                      "FROM ZZB.`Check` group by `IDNum`) ch on ch.`IDNum`=ci.`IDNum`";

    if(samename){
        filterHash = QHash<QString,QStringList>();
        sqlstring +="where `CadreName` in "
                   "(select `CadreName` from CadreInfo "
                   "group by `CadreName` having count(`CadreName`)>1) ";
    }
    // 任职时间过滤
    if(!filterHash.isEmpty()&& !filterHash["PostTime"].isEmpty()){
        QStringList v=filterHash["PostTime"];
        qDebug()<< v[0]<<v[1];
        sqlstring += QString(" where pmin.minposttime>= '%1' and pmin.minposttime<= '%2'")
                .arg(v[0],v[1]);
    }
    if(!filterHash.isEmpty()&& !filterHash["PostYear"].isEmpty()){
        QStringList v=filterHash["PostYear"];
        QString mindate=QDate::currentDate().addYears(-(v[1].toInt())).toString("yyyy-MM-dd");
        QString maxdate=QDate::currentDate().addYears(-(v[0].toInt())).toString("yyyy-MM-dd");
        sqlstring += QString(" where pmin.minposttime>= '%1' and pmin.minposttime<='%2'")
                .arg(mindate,maxdate);
    }
//    qDebug()<< sqlstring;
    tableModel->setQuery(sqlstring);

    // 获取各field的列号
    int cmincheckyear = tableModel->record().indexOf("mincheckyear");
    fieldcolumn["mincheckyear"]=cmincheckyear;
    int cmaxcheckyear = tableModel->record().indexOf("maxcheckyear");
    fieldcolumn["maxcheckyear"]=cmaxcheckyear;
    int ccountcheckyear = tableModel->record().indexOf("countcheckyear");
    fieldcolumn["countcheckyear"]=ccountcheckyear;

    int ceducation = tableModel->record().indexOf("Education");
    fieldcolumn["Education"]=ceducation;

    int cdegree= tableModel->record().indexOf("Degree");
    fieldcolumn["Degree"]=cdegree;

    int cunitgradationname = tableModel->record().indexOf("UnitGradationName");
    fieldcolumn["UnitGradationName"]=cunitgradationname;

    int cunitnatruename = tableModel->record().indexOf("UnitNatrueName");
    fieldcolumn["UnitNatrueName"]=cunitnatruename;

    int cunitcategory= tableModel->record().indexOf("UnitCategory");
    fieldcolumn["UnitCategory"]=cunitcategory;

    int cunitofcategoryname= tableModel->record().indexOf("UnitOfCategoryName");
    fieldcolumn["UnitOfCategoryName"]=cunitofcategoryname;

    int cunitlevel= tableModel->record().indexOf("UnitLevel");
    fieldcolumn["UnitLevel"]=cunitlevel;

    int csubunit= tableModel->record().indexOf("AffiliationName");
    fieldcolumn["AffiliationName"]=csubunit;

    int ccadrename = tableModel->record().indexOf("CadreName");
    fieldcolumn["CadreName"]=ccadrename;

    int csex = tableModel->record().indexOf("Sex");
    fieldcolumn["Sex"]=csex;

    int cpolitic = tableModel->record().indexOf("Politic");
    fieldcolumn["Politic"]=cpolitic;

    int cunitname = tableModel->record().indexOf("UnitName");
    fieldcolumn["UnitName"]=cunitname;

    int cpostname = tableModel->record().indexOf("PostName");
    fieldcolumn["PostName"]=cpostname;

    int cexperunitname = tableModel->record().indexOf("experUnitName");
    fieldcolumn["experUnitName"]=cexperunitname;

    int cexperpostname = tableModel->record().indexOf("experPostName");
    fieldcolumn["experPostName"]=cexperpostname;

    int cworkorschool = tableModel->record().indexOf("WorkORSchool");
    fieldcolumn["WorkORSchool"]=cworkorschool;

    int cmajor = tableModel->record().indexOf("Major");
    fieldcolumn["Major"]=cmajor;

    int cschool = tableModel->record().indexOf("School");
    fieldcolumn["School"]=cschool;

    int cfilenum = tableModel->record().indexOf("FileNum");
    fieldcolumn["FileNum"]=cfilenum;

    int cnativeplace = tableModel->record().indexOf("AdminArea");
    fieldcolumn["NativePlace"]=cnativeplace;

    int cbirthplace = tableModel->record().indexOf("BirthPlace");
    fieldcolumn["BirthPlace"]=cbirthplace;

    int cgrowplace = tableModel->record().indexOf("GrowPlace");
    fieldcolumn["GrowPlace"]=cgrowplace;

    int cbirth = tableModel->record().indexOf("Birth");
    fieldcolumn["Birth"]=cbirth;

    int cjoinworktime = tableModel->record().indexOf("JoinWorkTime");
    fieldcolumn["JoinWorkTime"]=cjoinworktime;

    int cpartytime = tableModel->record().indexOf("PartyTime");
    fieldcolumn["PartyTime"]=cpartytime;

    int cposttime = tableModel->record().indexOf("PostTime");
    fieldcolumn["PostTime"]=cposttime;
    int cranktime= tableModel->record().indexOf("RankTime");
    fieldcolumn["RankTime"]=cranktime;

    int cmanagestatus = tableModel->record().indexOf("ManageStatus");
    fieldcolumn["ManageStatus"]=cmanagestatus;
    int cthreeone = tableModel->record().indexOf("ThreeOne");
    fieldcolumn["ThreeOne"]=cthreeone;

    int fprofession = tableModel->record().indexOf("fprofession");
    fieldcolumn["fprofession"]=fprofession;
    int funitname = tableModel->record().indexOf("funitname");
    fieldcolumn["funitname"]=funitname;

    int fnation = tableModel->record().indexOf("Nation");
    fieldcolumn["Nation"]=fnation;
    int frankname = tableModel->record().indexOf("RankName");
    fieldcolumn["RankName"]=frankname;

    int cpostcategory= tableModel->record().indexOf("PostCategory");
    fieldcolumn["PostCategory"]=cpostcategory;

    int cminposttime = tableModel->record().indexOf("minposttime");
    fieldcolumn["minposttime"]=cminposttime;

    proxyModel = new RollCadreSortFilterProxyModel(this);
    proxyModel->setDynamicSortFilter(true);
    proxyModel->setSourceModel(tableModel);
    proxyModel->columnhash = fieldcolumn;
    tableModel->chash = fieldcolumn;

    ui->resultTableView->setModel(proxyModel);
    setFilter();
    proxyModel->setHeaderData(0, Qt::Horizontal, QString::fromUtf8("选择"));
    proxyModel->setHeaderData(1, Qt::Horizontal, QString::fromUtf8("姓名"));
    proxyModel->setHeaderData(4, Qt::Horizontal, QString::fromUtf8("证件号码"));
    proxyModel->setHeaderData(5, Qt::Horizontal, QString::fromUtf8("出生日期"));
    proxyModel->setHeaderData(7, Qt::Horizontal, QString::fromUtf8("性别"));
    proxyModel->setHeaderData(cunitname, Qt::Horizontal, QString::fromUtf8("单位"));
    proxyModel->setHeaderData(cpostname, Qt::Horizontal, QString::fromUtf8("职务"));
    proxyModel->setHeaderData(cminposttime, Qt::Horizontal, QString::fromUtf8("现职时间"));
    proxyModel->setHeaderData(frankname, Qt::Horizontal, QString::fromUtf8("级别"));
    proxyModel->setHeaderData(cranktime, Qt::Horizontal, QString::fromUtf8("级别时间"));

    ui->resultTableView->horizontalHeader()->setVisible(true);
    QHash<int,QString> showidlist = uniqueCadre();
    int count=proxyModel->columnCount();
    for(int i=0;i<count;i++){
        if(i==0|| i==1 || i==4 || i==5 || i==7 || i==cpostname || i==cunitname
                || i==frankname || i==cranktime || i==cminposttime ){
        }else{
            ui->resultTableView->setColumnHidden(i,true);
        }
    }
    TimeEditDelegate *birthtime = new TimeEditDelegate();
    ui->resultTableView->setItemDelegateForColumn(5,birthtime);
    TimeEditDelegate *minposttime = new TimeEditDelegate();
    ui->resultTableView->setItemDelegateForColumn(cminposttime,minposttime);
    TimeEditDelegate *ranktime = new TimeEditDelegate();
    ui->resultTableView->setItemDelegateForColumn(cranktime,ranktime);

    ui->resultTableView->setColumnWidth(cpostname,300);
    int compute=0;
    if(!cadrename.isEmpty()){
        foreach(int index,showidlist.keys()){
            if((proxyModel->data(proxyModel->index(index,1)).toString()).contains(cadrename)){
                compute++;
               if(proxyModel->setData(proxyModel->index(index,1),QColor(Qt::lightGray),
                                      Qt::BackgroundRole)){
                   qDebug()<<"success";
               }
            }
        }
    }
    compute=compute>0?(showidlist.size()-compute):showidlist.size();
    ui->totallineEdit->setText(QString::number(compute));
}
// 所有相同IDNum的就显示一列
QHash<int,QString> RollCadreSearchResultDialog::uniqueCadre()
{
    QHash<int,QString> idlist,newidlist;
    QList<int> showids;

    int rowcount = proxyModel->rowCount();
    int idcolumn = tableModel->record().indexOf("IDNum");
    for(int i=0;i<rowcount;i++){
        idlist[i]=proxyModel->data(proxyModel->index(i,idcolumn)).toString();
    }
    foreach(QString value,idlist.values().toSet() ){
        showids << idlist.key(value); // 只返回一个key
        newidlist[idlist.key(value)]=value;
    }

    for(int i=0;i<rowcount;i++){

        ui->resultTableView->setRowHidden(i,false);// 特别重要，要是不加此句，清除查询条件后，再按搜索table不会更新,或者显示不全

        if(!showids.contains(i)){
            ui->resultTableView->setRowHidden(i,true);
        }

    }
    return newidlist;
}

void RollCadreSearchResultDialog::on_okPushButton_clicked()
{
    QString sqlstring ="insert into CadreRollRelation (`RollID`,`IDNum`) "
                       "select ?, ? from DUAL where not EXISTS ("
                       "select `RollID`,`IDNum` from CadreRollRelation "
                       "where `RollID`= ? and `IDNum`= ? )";

    RollCadreSearchDialog *p = (RollCadreSearchDialog*)parentWidget();
    CadreRoll *ppp = (CadreRoll *)(p->parentWidget()->parentWidget());
    QStringList idList;
    for(int i=0;i<proxyModel->rowCount();i++){
        int ischecked;
       ischecked= proxyModel->data(proxyModel->index(i,0),Qt::CheckStateRole).toInt();
       //Qt::checked 对应的数值就是2; 如果选定，则把证件号码提取出来
       if(ischecked==2){
          idList<< proxyModel->data(proxyModel->index(i,4)).toString();
       }
    }
    int rollid;
    if(ppp->flags=="add")
    {
        RollCreateDialog *pp = (RollCreateDialog*)p->parentWidget();
        rollid = pp->addId;
        QSqlQuery query;
        query.prepare(sqlstring);
        QVariantList ids,rids;
        for (int i=0;i<idList.size();i++){
            ids.append(idList[i]);
            rids.append(rollid);
        }
        query.addBindValue(rids);
        query.addBindValue(ids);
        query.addBindValue(rids);
        query.addBindValue(ids);

        if (!query.execBatch())
            qDebug() << query.lastError();
        else{
            connect(this, SIGNAL(sendIdData(QStringList)),pp,
                    SLOT(receiveIdData(QStringList)));
            emit sendIdData(idList);
            QDialog::reject();
        }

    }
    if(ppp->flags=="edit")
    {
        RollEditDialog *pp = (RollEditDialog*)p->parentWidget();
        rollid = pp->roll;
        QSqlQuery query;
        query.prepare(sqlstring);
        QVariantList ids,rids;
        for (int i=0;i<idList.size();i++){
            ids.append(idList[i]);
            rids.append(rollid);
        }
        query.addBindValue(rids);
        query.addBindValue(ids);
        query.addBindValue(rids);
        query.addBindValue(ids);
        if (!query.execBatch())
            qDebug() << query.lastError();
        else{
            connect(this, SIGNAL(sendIdData(QStringList)),pp,
                    SLOT(receiveIdData(QStringList)));
            emit sendIdData(idList);
            QDialog::reject();
        }
    }
}

void RollCadreSearchResultDialog::on_allpushButton_clicked()
{
    for(int i=0;i<proxyModel->rowCount();i++){
        proxyModel->setData(proxyModel->index(i,0),QVariant(Qt::Checked),Qt::CheckStateRole);
        // 必须刷新，否则界面不显示
        ui->resultTableView->update(proxyModel->index(i,0));
    }
}

void RollCadreSearchResultDialog::on_searchpushButton_clicked()
{
    QString name = ui->cadrelineEdit->text();
    initResultTable(name);
}
